package com.kcsj.dao;
//201902104020 李鑫元
import com.kcsj.dao.User.EmployeeDao;
import com.kcsj.domain.Order;
import com.kcsj.domain.WayBill;
import com.kcsj.domain.user.Employee;
import com.kcsj.util.JdbcHelper;

import java.lang.reflect.Field;
import java.sql.*;
import java.time.format.DateTimeFormatter;
import java.util.*;

public class WayBillDao {
    public static WayBillDao wayBillDao = new WayBillDao();
    public static WayBillDao getInstance() {return wayBillDao;}
    public boolean add(WayBill wayBill, Connection conn) throws SQLException {
        //使用预编译创建SQL语句
        String create = "call sp_wayBill(?,?,?,?)";
        PreparedStatement statement = conn.prepareStatement(create);
        //执行SQL语句并返回结果和关闭连接
        try {
            statement.setString(1, wayBill.getWayBillNo());
            statement.setString(3, wayBill.getOrder().getOrderNo());
            statement.setString(2, wayBill.getWaybillStatus());
            statement.setInt(4, wayBill.getEmployee().getId());
            int rowsAffected = statement.executeUpdate();
            return rowsAffected > 0;
        }finally {
            JdbcHelper.close(statement);
        }
    }
    public boolean delete(String WayBillNo, Connection conn) throws SQLException {
        //创建删除语句
        String delete = "delete from WayBill where WayBillNo = " + WayBillNo;
        //在连接上创建语句盒子对象
        Statement statement = conn.createStatement();
        //执行SQL语句
        int i = statement.executeUpdate(delete);
        //关闭连接
        JdbcHelper.close(statement,conn);
        return (i>0);
    }
    public boolean update(WayBill wayBill, Connection conn) throws SQLException {
        //使用预编译创建SQL语句
        String update = "update waybill set wayBillStatus = ?,employee_id = ? " +
                " where WaybillNo = " + wayBill.getWayBillNo();
        System.out.println(update);
        PreparedStatement statement = conn.prepareStatement(update);
        //执行SQL语句并返回结果和关闭连接
        try {
            statement.setString(1, wayBill.getWaybillStatus());
            statement.setInt(2, wayBill.getEmployee().getId());
            int rowsAffected = statement.executeUpdate();
            return rowsAffected > 0;
        }finally {
            JdbcHelper.close(statement);
        }
    }

    public WayBill find(String orderNo, Connection conn) throws SQLException {
        //sql语句
        String find_sql = "SELECT * FROM WayBill where orderForm_No = ? ";
        PreparedStatement statement = conn.prepareStatement(find_sql);
        statement.setString(1,orderNo);
        ResultSet resultSet = statement.executeQuery();
        WayBill wayBill = null;
        if (resultSet.next()) {
            int employee_id = resultSet.getInt("employee_id");
            Timestamp timestamp = resultSet.getTimestamp("createDatetime");
            DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
            wayBill = new WayBill(
                    resultSet.getString("wayBillNo"),
                    OrderDao.getInstance().find(orderNo,conn),
                    resultSet.getString("waybillStatus"),
                    timestamp.toLocalDateTime().format(dtf),
                    resultSet.getString("estimatedArrivalTime"),
                    EmployeeDao.getInstance().findInfo(employee_id,conn)
            );
        }
        JdbcHelper.close(statement);
        return wayBill;
    }

    public Collection<WayBill> findAll(Integer id, Connection conn) throws SQLException {
        //创建集合类对象，用来保存并排序所有获得的department对象
        Collection<WayBill> wayBills = new LinkedHashSet<>();
        //创建查询的主句
        StringBuilder select = new StringBuilder("SELECT * from WayBill ");
        //在连接上创建语句盒子对象
        PreparedStatement statement = conn.prepareStatement(select.toString());
        //执行SQL语句
        ResultSet results = statement.executeQuery();
        //遍历resultSet，并将结果写入对象存进集合内
        while (results.next()){
            String wayBillNo = results.getString("waybillNo");
            String wayBillStatus = results.getString("wayBillStatus");
            Timestamp CreateTime = results.getTimestamp("createDatetime");
            String estimatedArrivalTime = results.getString("estimatedArrivalTime");
            Order order = OrderDao.getInstance().find(results.getString("orderform_No"),conn);
            Employee employee = EmployeeDao.getInstance().findInfo(
                    results.getInt("employee_id"), conn);
            DateTimeFormatter WB = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
            WayBill wayBill = new WayBill(wayBillNo, order,wayBillStatus,
                    CreateTime.toLocalDateTime().format(WB),estimatedArrivalTime,employee);
            wayBills.add(wayBill);
        }
        //关闭资源
        JdbcHelper.close(results,statement,null);
        //返回获得的集合对象
        return wayBills;
    }

    public boolean updateStatus(String orderNo,
                                String status,
                                Connection conn) throws SQLException {
        //sql语句
        String delay_sql = "update waybill set waybillStatus=? where orderform_No = ?";
        PreparedStatement pstmt = conn.prepareStatement(delay_sql);
        pstmt.setString(1,status);
        pstmt.setString(2,orderNo);
        int rowsAffected = pstmt.executeUpdate();
        JdbcHelper.close(pstmt);
        return rowsAffected>0;
    }
//指派配送员的方法
    public Employee getDeliveryMan(Connection conn) throws SQLException, IllegalAccessException, NoSuchFieldException {
        //再针对每个快递员计量他们当前的任务量
        String countTask = "Select count(employee_id) AS number from waybill where employee_id=?;";
        //在连接上创建语句盒子对象
        PreparedStatement statement = conn.prepareStatement(countTask);
        Map<Integer,Integer> deliveryManWithTask = new LinkedHashMap<>();
        for (Integer employee_id : getAllDeliveryMan(conn)) {
            statement.setInt(1,employee_id);
            //执行SQL语句
            ResultSet results = statement.executeQuery();
            while(results.next()){
                deliveryManWithTask.put(employee_id,results.getInt("number"));
            }
        }
        Map<Integer,Integer> deliveryManWithFinishedTask = wayBillDao.getFinishedTask(conn);
        for(Map.Entry<Integer,Integer> entry : deliveryManWithTask.entrySet()) {
            entry.setValue(entry.getValue()-deliveryManWithFinishedTask.get(entry.getKey()));
        }
        //最终用于返回的Map
        Map<Integer,Integer> finallyy = new LinkedHashMap<>();
        for (Map.Entry<Integer,Integer> entry : deliveryManWithTask.entrySet()) {
            finallyy.put(entry.getValue(),entry.getKey());
        }
        Field tail = finallyy.getClass().getDeclaredField("tail");
        tail.setAccessible(true);
        Map.Entry<Integer,Integer> entry=(Map.Entry<Integer, Integer>) tail.get(finallyy);
        Integer value = entry.getValue();
        Employee employee = EmployeeDao.getInstance().findInfo(value,conn);
        return employee;
    }
    public Collection<Integer> getAllDeliveryMan(Connection conn) throws SQLException {
        //首先找出所有快递员
        String findAllDeliveryMan = "SELECT employee.id from employee,`user` where  `user`.id = employee.id and `user`.role_id=6;";
        //在连接上创建语句盒子对象
        PreparedStatement statement = conn.prepareStatement(findAllDeliveryMan);
        //执行SQL语句
        ResultSet results = statement.executeQuery();
        Collection<Integer> deliveryMen = new LinkedList<>();
        while(results.next()){
            deliveryMen.add(results.getInt("id"));
        }
        return deliveryMen;
    }
    public Map<Integer,Integer> getFinishedTask(Connection conn) throws SQLException {
        //再针对每个快递员计量他们当前的任务量
        String countFinished = "Select count(employee_id) AS number from waybill where employee_id=? and waybillStatus = '已送达';";
        //在连接上创建语句盒子对象
        PreparedStatement statement = conn.prepareStatement(countFinished);
        Map<Integer,Integer> deliveryManWithFinishedTask = new LinkedHashMap<>();
        for (Integer employee_id : getAllDeliveryMan(conn)) {
            statement.setInt(1,employee_id);
            //执行SQL语句
            ResultSet results = statement.executeQuery();
            while(results.next()){
                deliveryManWithFinishedTask.put(employee_id,results.getInt("number"));
            }
        }
        return deliveryManWithFinishedTask;
    }
}
